IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetValueTableOfDelimittedStrings]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetValueTableOfDelimittedStrings]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************                
* Name:  [GetValueTableOfDelimittedStrings]             
* Purpose:  Returns a table output from the string containing the delimited values                
*                
* PARAMETERS                
* Name(Input)        Description                     
* -------------   -------------------------------------------                
* @delimittedString1 Delimited string 1 that needs to be processed                
* @delimittedString2 Delimited string 2 that needs to be processed
* @delimittedString3 Delimited string 3 that needs to be processed     
* @delimchar1   Delimitting character 1 (used in @delimittedString1)        
* @delimchar2   Delimitting character 2 (used in @delimittedString2)  
* @delimchar3   Delimitting character 3 (used in @delimittedString3)            
                
* PARAMETERS                
* Name(Output)        Description                     
* -------------    -------------------------------------------                
* @Table            return the table constructed from the Delimitted string                
******************************************************************/                
CREATE FUNCTION [GetValueTableOfDelimittedStrings]             
       (            
  @delimittedString1 varchar(max),            
  @delimittedString2 varchar(max),   
  @delimittedString3 varchar(max),  
  @delimchar1 varchar(1),    
  @delimchar2 varchar(1),
  @delimchar3 varchar(1)      
       )            
RETURNS @Table TABLE (    
       [Id] int IDENTITY(1, 1) NOT NULL,            
       [Value1]  varchar(20),    
       [Value2]  varchar(20),
	   [Value3]  varchar(20)     
       )             
AS            
BEGIN            
            
  Declare @value1 varchar(20),    
		  @value2 varchar(20),
		  @value3 varchar(20),    
		  @delimitter1Position bigint,    
		  @delimitter2Position bigint,
		  @delimitter3Position bigint   
        
  Set @delimitter1Position = PatIndex('%'+@delimchar1+'%',@delimittedString1)-1    
  Set @delimitter2Position = PatIndex('%'+@delimchar2+'%',@delimittedString2)-1
  Set @delimitter3Position = PatIndex('%'+@delimchar3+'%',@delimittedString3)-1       
    
  If @delimitter1Position = -1    
    INSERT @Table (Value1,Value2,value3) VALUES (@delimittedString1 , @delimittedString2,@delimittedString3)             
    
  WHILE (@delimitter1Position > 0 or @delimitter2Position >0 or @delimitter3Position >0)    
   BEGIN    
    Set @value1 = Substring(@delimittedString1,1,@delimitter1Position)    
    Set @value2 = Substring(@delimittedString2,1,@delimitter2Position)  
	Set @value3 = Substring(@delimittedString3,1,@delimitter3Position)   
  if @value1 = ''  
 Set @value1 = null  
  if @value2 = ''  
 Set @value2 = null  
  if @value3 = ''  
 Set @value3 = null  
   
      INSERT @Table (Value1,Value2,value3) VALUES (@value1 , @value2, @value3 )    
      Set @delimittedString1 = Substring(@delimittedString1,@delimitter1Position+2,Len(@delimittedString1))    
      Set @delimittedString2 = Substring(@delimittedString2,@delimitter2Position+2,Len(@delimittedString2))    
	  Set @delimittedString3 = Substring(@delimittedString3,@delimitter3Position+2,Len(@delimittedString3))    
      if @delimittedString1 <> '' or @delimittedString2 <> '' or @delimittedString3 <> ''   
       BEGIN    
        Set @delimitter1Position = PatIndex('%'+@delimchar1+'%',@delimittedString1)-1    
        Set @delimitter2Position = PatIndex('%'+@delimchar2+'%',@delimittedString2)-1  
		Set @delimitter3Position = PatIndex('%'+@delimchar3+'%',@delimittedString3)-1   
        If @delimitter1Position = -1   
		  BEGIN      
		  if @delimittedString1 = ''  
		   Set @delimittedString1 = null  
		  if @delimittedString2 = ''  
		   Set @delimittedString2 = null 
		  if @delimittedString3 = ''  
		   Set @delimittedString3 = null  
				 INSERT @Table (Value1,Value2,value3) VALUES ( @delimittedString1 , @delimittedString2,@delimittedString3)  
		  END             
       END    
          
   END    
  RETURN            
END     
  
                    